﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Windows.Forms;
using System.IO;
using System.Data.SqlClient;
using System.Threading;
using System.Diagnostics;

namespace LoadScript
{
    public class SQLScriptLoaderRelease
    {
        // private const string CONN_STRING = @"server=localhost;initial catalog=resto;Persist Security Info=False;Integrated Security=False;user id=sa;password=eprojeco";
       // private string CONN_STRING = ConfigurationManager.AppSettings["ConnectionString"];
        //  private static  string LOG_PATH = @"c:\temp\ScriptLoad"+ Utils.DateYYMDString(DateTime.Today) + ".log";
        private StreamWriter streamWriter;
        private string loginName, password, serverName, batFilePath, connStr, scriptPath,dbName;

      //  public static string BATFILE_PATH = @"c:\temp\abc.bat";
        Process pro;
        private static List<string> historyList=new List<string>();//已经执行过的目录列表，在构造方法里初始化
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sqlConnectionString">Connection string</param>
        /// <param name="batFilePath">the generated bat file's path</param>
        /// <param name="loginName">your database's login name</param>
        /// <param name="password">your database's password</param>
        /// <param name="serverName">your database'host name</param>
        //public SQLScriptLoaderRelease(string batFilePath, string loginName, string password, string serverName,string dbname)
        //{
            
        //    this.batFilePath = batFilePath;

        //    this.loginName = loginName;
        //    this.password = password;
        //    this.serverName = serverName;
        //    this.dbName = dbname;
        //    //SQLScriptLoaderRelease.historyList = SelectFileName();//数据库已经存在的列表;
        //}

        public SQLScriptLoaderRelease(string batFilePath, string connStr,string scriptPath)
        {
            this.scriptPath = scriptPath;
            this.batFilePath = batFilePath + "\\abc.bat";
            this.connStr = connStr;
            this.loginName = GetLoginName(connStr);
            this.password = GetPassword(connStr);
            this.serverName = GetServerName(connStr);
            this.dbName = GetDBName(connStr);
            //SQLScriptLoaderRelease. historyList = SelectFileName();//数据库已经存在的列表;
        }
        //public SQLScriptLoaderRelease(string batFilePath,  string scriptPath ,string serverName,string dbname)
        //{
        //    this.scriptPath = scriptPath;
        //    this.batFilePath = batFilePath + "\\abc.bat";
        //    this.loginName = GetLoginName(connStr);
        //    this.password = GetPassword(connStr);
        //    this.serverName = serverName;
        //    this.dbName = dbname;
        //    //SQLScriptLoaderRelease. historyList = SelectFileName();//数据库已经存在的列表;
        //}
        [STAThread]
        static void Main()
        {
            Frm_Config1 frm = new Frm_Config1();
            frm.ShowDialog();
        }

        public void Load()
        {
            LoadScriptInMaster(this.scriptPath );
            LoadScriptExceptMaster(this.scriptPath);
            this .streamWriter.Flush();
            this.streamWriter.Close();
        }

        private void LoadScriptInMaster(string path)
        {
            string[] directories = Directory.GetDirectories(path);
            for (int i = 0; i < directories.Length; i++)
            {
                if (directories[i].ToLower().EndsWith("master"))
                {

                    string[] directoriesMaster = Directory.GetDirectories(directories[i]);//遍历master目录
                    for (int j = 0; j < directoriesMaster.Length; j++)
                    {
                        if (directoriesMaster[j].ToLower().EndsWith(".svn"))
                        {
                            continue;
                        }
                        if (historyList.Contains(directories[i].Substring(directories[i].LastIndexOf(@"\") + 1)))//如果已经包含，则直接进入下一个循环
                        {
                            continue;
                        }
                        else
                        {
                            //this.Log(directoriesMaster[j]);//增加一条日志记录
                            string[] fileNames = Directory.GetFiles(directoriesMaster[j]);
                           // ns.NumericComparer ns = new ns. NumericComparer(); // new object
                            Array.Sort(fileNames, StringLogicalComparer.DefaultZeroesFirst);
                          //  Array.Sort(fileNames,ns);
                            for (int k = 0; k < fileNames.Length; k++)
                            {
                                if (!fileNames[k].EndsWith(".sql"))
                                {
                                    continue;
                                }
                                this.AppendCmd(fileNames[k], dbName);

                            }

                        }
                    }
                }
            }
        }

        /// <summary>
        /// run all the scripts out of the master folder
        /// </summary>
        /// <param name="sqlConnectionString"></param>
        public void LoadScriptExceptMaster(string path)
        {
            string[] directories = Directory.GetDirectories(path);
            string[] files = Directory.GetFiles(path);
            //Array.Sort(directories);
            
            // Array.Sort(fileNames);
            Array.Sort(files, StringLogicalComparer.DefaultZeroesFirst);
            //现在新的historyList只包含最后的日期字符串
            //也需要directories[i]最后的日期字符串截出来用于判断

            for (int j = 0; j < files.Length; j++)
            {
                if (!files[j].EndsWith(".sql"))
                {
                    continue;
                }
                this.AppendCmd(files[j], dbName);
            }
            for (int i = 0; i < directories.Length; i++)
            {
                if (directories[i].EndsWith(".svn") || directories[i].EndsWith("Master"))

                {
                    continue;
                }
                string[] fileNames = Directory.GetFiles(directories[i]);
               // Array.Sort(fileNames);
                Array.Sort(fileNames, StringLogicalComparer.DefaultZeroesFirst);
                //现在新的historyList只包含最后的日期字符串
                //也需要directories[i]最后的日期字符串截出来用于判断
                //if (historyList.Contains(directories[i]))//如果已经包含，则直接进入下一个循环\
                if (historyList.Contains(directories[i].Substring(directories[i].LastIndexOf (@"\")+1)))//如果已经包含，则直接进入下一个循环
                {
                    continue;
                }
                else
                {
                    //this.Log(directories[i]);//增加一条日志记录
                    //string[] fileNames = Directory.GetFiles(directories[i]);
                    for (int j = 0; j < fileNames.Length; j++)
                    {
                        if (!fileNames[j].EndsWith(".sql"))
                        {
                            continue;
                        }
                        this.AppendCmd(fileNames[j], dbName);
                    }
                }
            }
        }

        private bool RunScript(string path)
        {
            FileInfo file = new FileInfo(path);
            try
            {
                RunScript(file);
                return true;
            }
            catch (Exception ex)
            {
                //   Utils.logger.Info(ex.Message + ex.StackTrace);
                return false;
            }

        }
        public void RunScript(FileInfo file)
        {
            try
            {
                string script = file.OpenText().ReadToEnd().ToString();
                // dbCls.runSQL(script);
            }
            catch (Exception ex)
            {
                // MessageBox.Show(ex.Message + ex.StackTrace);
                //    Utils.logger.Info(ex.Message + ex.StackTrace);
                throw ex;
            }
        }
        private void CreateFolder(string originalPath, string subDir)
        {
            string newPath = Path.Combine(originalPath, subDir);
            // Create the subfolder
            if (!Directory.Exists(newPath))
            {
                Directory.CreateDirectory(newPath);
            }

        }
        public  void CreateBatFile ()
        {
            streamWriter = new StreamWriter(this.batFilePath );
        }
 
        private string AppendCmd(string inputPath,string db)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("sqlcmd -U ");
            sb.Append(loginName);
            sb.Append(" ");
            sb.Append("-P ");
            sb.Append(password);
            sb.Append(" ");
            sb.Append("-S ");
            sb.Append(serverName);
            sb.Append(" ");
            if (!string.IsNullOrEmpty(db))
            {
                sb.Append("-d ");
                sb.Append(db);
                sb.Append(" ");
            }
            sb.Append("-i ");
            sb.Append(inputPath);
            sb.Append(" ");
            sb.Append("-o ");
            sb.Append(inputPath.Replace(".sql", ".txt"));
            sb.Append("\n");
            string result = sb.ToString();
            streamWriter.Write(result);
            streamWriter.Flush();
            return result;
            // streamWriter.Close();
            //sqlcmd -U sa -P eprojeco -S EPROJECO-PC -i C:\Pos\Packaging\dbScript\input\1-Tables\10-PrinterCommand.sql -o C:\Pos\Packaging\dbScript\output\1-Tables\10-PrinterCommand.txt
        }
        private void OpenCmdWindow()
        {
            this.pro = new Process();
            pro.StartInfo.FileName = "cmd.exe";
            pro.StartInfo.UseShellExecute = false;
            pro.StartInfo.RedirectStandardInput = true;
            pro.StartInfo.RedirectStandardOutput = true;
            pro.StartInfo.RedirectStandardError = true;
            pro.StartInfo.CreateNoWindow = false;
            pro.Start();
        }
        private void CloseCmdWindow()
        {
            pro.StandardInput.WriteLine("exit" + "\n");
        }
        private void Cmd(string cmd)
        {
            pro.StandardInput.WriteLine(cmd);
            Console.WriteLine(cmd);
            Thread.Sleep(2000);
            // pro.StandardInput.WriteLine("exit");
        }
        public  void RunBatFile()
        {
            FileInfo file = new FileInfo(this.batFilePath);
            if (file.Length > 0)
            { 
            ProcessStartInfo p = new ProcessStartInfo(this.batFilePath );
            Process proc = new Process();
            proc.StartInfo = p;
            proc.Start();
            proc.WaitForExit();
            }
        }

        /// <summary>
        /// 把已经运行过的脚本的目录写到数据库
        /// </summary>
        /// <param name="fileName"></param>
        private void Log(string fileName)
        {
            try
            {
                string sql = "insert into ScriptLoadingLog values(@fileName,@DateTime)";
                // string sql = "insert into ScriptLoadingLog (FileName) values ('" + fileName + "')";
                using (SqlConnection conn = new SqlConnection(this.connStr ))
                {
                    conn.Open();
                    SqlCommand cmd = new SqlCommand(sql, conn);

                    cmd.Parameters.AddWithValue("@fileName", fileName);
                    cmd.Parameters.AddWithValue("@DateTime", DateTime.Now);
                    cmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message + ex.StackTrace);
            }
        }

        /// <summary>
        /// 从数据库检索出所有已经运行过的脚本目录
        /// </summary>
        /// <returns></returns>
        private List<string> SelectFileName()
        {
            string sql = "select distinct FileName from ScriptLoadingLog";
            using (SqlConnection conn = new SqlConnection(this.connStr ))
            {
                conn.Open();
                List<string> list = new List<string>();
                SqlCommand cmd = new SqlCommand(sql, conn);

                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    //在这里添加截取最后的字符串
                    string LastFileName = reader["FileName"].ToString().Split('\\')[reader["FileName"].ToString().Split('\\').Length -1];
                    list.Add(LastFileName);
                    //list.Add(reader["FileName"].ToString());
                }
                return list;
            }
        }

        public static string GetPassword(string conn)
        {
            string  password = "password";
            string[] s = conn.Split(';');
            for (int i = 0; i < s.Length; i++)
            {
                if (  s[i].ToLower ().Trim ().IndexOf(password) == 0)
                {
                    return s[i].Substring(s[i].LastIndexOf("=") + 1);
                }

            }
            return "eprojeco";
        }

        public static  string   GetLoginName(string conn)
        {
            string id = "user id";// password = "password";
            //conn.Trim();
            string[] s = conn.Split(';');
            for (int i = 0; i < s.Length; i++)
            {
                if (s[i].Trim ().ToLower() .IndexOf(id) == 0 )
                {
                    return s[i].Substring (s[i].LastIndexOf ("=")+1);
                }

            }
            return "sa";
        }
        public static string GetDBName(string conn)
        {
            string  catalog = "initial catalog";// id = "user id", password = "password";
            conn.Trim();
            string[] s = conn.Split(';');
            for (int i = 0; i < s.Length; i++)
            {
                if ( s[i].ToLower ().Trim ().IndexOf(catalog) == 0 )
                {
                    return s[i].Substring(s[i].LastIndexOf("=") + 1);
                }

            }
            return null;
        }

        public static string GetServerName(string conn)
        {
            string server = "server";// catalog = "initial catalog", id = "user id", password = "password";
            conn.Trim();
            string[] s = conn.Split(';');
            for (int i = 0; i < s.Length; i++)
            {
                if (s[i].ToLower ().Trim () .IndexOf(server) == 0 )
                {
                    return s[i].Substring(s[i].LastIndexOf("=") + 1);
                }

            }
            return "localhost";
        }

    }
}


